package dao.impl;

import dao.UserDao;
import domain.AdminUser;
import util.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 于金鹏
 * @Date 2024/12/16 14:54
 * @Package StudentCheckSystem
 */
public class UserDaoImpl implements UserDao {
    @Override
    public AdminUser getUserByUsername(String username) throws SQLException {
        // 获取连接
        Connection connection = JdbcUtils.getConnection();
        // 编写SQL语句
        String sql = "select * from system_user where username = ?";
        // 预编译SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        preparedStatement.setString(1, username);
        // 执行SQL语句
        ResultSet resultSet = preparedStatement.executeQuery();
        // 处理结果集
        if (resultSet.next()) {
            AdminUser adminUser = new AdminUser();
            adminUser.setUserId(resultSet.getInt("admin_id"));
            adminUser.setUserName(resultSet.getString("username"));
            adminUser.setSex(resultSet.getInt("sex"));
            adminUser.setPassword(resultSet.getString("admin_password"));
            adminUser.setEmail(resultSet.getString("email"));
            adminUser.setPhone(resultSet.getString("phone"));
            return adminUser;
        }
        preparedStatement.close();
        connection.close();
        return null;

    }

    @Override
    public int addUser(AdminUser adminUser) throws SQLException {
        // 获取数据库连接
        Connection connection = JdbcUtils.getConnection();
        // 编写SQL语句
        String sql = "insert into system_user (username, sex, admin_password, email, phone) values (?, ?, ?, ?, ?)";
        // 创建PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        int i = 1;
        preparedStatement.setString(i++, adminUser.getUserName());
        preparedStatement.setInt(i++, adminUser.getSex());
        preparedStatement.setString(i++, adminUser.getPassword());
        preparedStatement.setString(i++, adminUser.getEmail());
        preparedStatement.setString(i++, adminUser.getPhone());
        int result = preparedStatement.executeUpdate();
        // 关闭资源
        preparedStatement.close();
        connection.close();
        return result;

    }

    @Override
    public List<AdminUser> getAllUsers(AdminUser adminUser) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "select * from system_user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<AdminUser> list = new ArrayList<>();
        preparedStatement.close();
        connection.close();
        return list;
    }

    @Override
    public int deleteUserByUserId(int id) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "delete from system_user where admin_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        int result = preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
        return result;
    }

    @Override
    public int updateUserByUserId(AdminUser adminUser) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "update system_user set username = ?, sex = ?, admin_password = ?, email = ?, phone = ? where admin_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int i = 1;
        preparedStatement.setString(i++, adminUser.getUserName());
        preparedStatement.setInt(i++, adminUser.getSex());
        preparedStatement.setString(i++, adminUser.getPassword());
        preparedStatement.setString(i++, adminUser.getEmail());
        preparedStatement.setString(i++, adminUser.getPhone());
        preparedStatement.setInt(i++, adminUser.getUserId());
        int result = preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
        return result;
    }

    @Override
    public AdminUser getUserByUserId(int id) throws SQLException {
        // 获取数据库连接
        Connection connection = JdbcUtils.getConnection();
        // 编写SQL语句
        String sql = "select * from system_user where admin_id = ?";
        // 创建PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        preparedStatement.setInt(1, id);
        // 执行查询
        ResultSet resultSet = preparedStatement.executeQuery();
        // 调用封装获取结果
        List<AdminUser> list = getAdminUserFromResultSet(resultSet);
        // 关闭资源
        preparedStatement.close();
        connection.close();
        // 返回结果
        if (!list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<AdminUser> UserPage(AdminUser adminUser, int pageNum, int pageSize) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "select * from system_user limit ?, ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int i = 1;
        preparedStatement.setInt(i++, (pageNum - 1) * pageSize);
        preparedStatement.setInt(i++, pageSize);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<AdminUser> list = getAdminUserFromResultSet(resultSet);
        preparedStatement.close();
        connection.close();
        return list;
    }

    @Override
    public int count(AdminUser adminUser) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "select count(*) as total from system_user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        int total = 0;
        while (resultSet.next()){
            total = resultSet.getInt("total");
        }
        preparedStatement.close();
        connection.close();
        return total;
    }

    private List<AdminUser> getAdminUserFromResultSet(ResultSet resultSet) throws SQLException {
        List<AdminUser> list = new ArrayList<>();
        while (resultSet.next()) {
            list.add(new AdminUser(
                    resultSet.getInt("admin_id"),
                    resultSet.getString("username"),
                    resultSet.getInt("sex"),
                    resultSet.getString("admin_password"),
                    resultSet.getString("email"),
                    resultSet.getString("phone")
            ));
        }
        return list;
    }
}
